﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using MyLib;

namespace Portal
{
    public partial class FromEbs : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        //查询
        protected void Button1_Click(object sender, EventArgs e)
        {
            string ContractNum = ContractNo.Text.ToString().Trim();//合同编号
            if (string.IsNullOrEmpty(ContractNum))
            {

                Response.Write("<script type='text/javascript'>alert('请填写合同编号！');</script>");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"select a.htbh,a.gysmc,a.note,a.htje,a.htsl,a.rksl,decode(a.ykpje1,0,a.ykpje2,null,a.ykpje2,'',a.ykpje2,a.ykpje1) ykpje,decode(a.yfje1,0,a.yfje2,null,a.yfje2,'',a.yfje2,a.yfje1) yfje from (SELECT ph.segment1 htbh,
       aps.vendor_name gysmc,
       ph.comments note,
       round(SUM(pla.unit_price*(plla.quantity-nvl(plla.quantity_cancelled,0)))*(1+apps.cux_po_receive_report_pkg.get_vendor_tax(ph.vendor_site_id)/100),2) htje,--合同金额，不含税
       SUM(plla.quantity-nvl(plla.quantity_cancelled,0)) htsl,--合同数量 
       SUM(nvl(plla.QUANTITY_RECEIVED,0)) rksl,--入库数量 
       round(invoiceinfo.invoice_amount,2) ykpje1,
       round(invoiceinfo.amount_paid,2) yfje1,
       round(nvl(expenseinfo.expense_amount,0),2) ykpje2,
       round(nvl(expenseinfo.amountpaid_expense,0),2) yfje2
   FROM 
      po_headers_all ph,
      po_lines_all   pla,
      po_line_locations_all plla,
      ap_suppliers   aps,
      (
         SELECT SUM(nvl(aia.invoice_amount,0)) invoice_amount,SUM(nvl(aia.amount_paid,0)) amount_paid FROM ap_invoices_all aia
           WHERE aia.Invoice_Id IN
           ( SELECT aila.Invoice_Id FROM ap_invoice_lines_all aila,
                                         po_headers_all       pha
               WHERE  aila.po_header_id=pha.po_header_id
                 AND  pha.segment1 ='{0}'                         
             )
       ) invoiceinfo,
      (
         SELECT SUM(nvl(decode(aia.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',0,aia.invoice_amount),0)) expense_amount,
                SUM(nvl(aia.amount_paid,0)) amountpaid_expense FROM ap_invoices_all aia
           WHERE 
               aia.po_header_id IN
                (SELECT pha.po_header_id FROM  po_headers_all       pha
                 WHERE  pha.segment1 ='{1}'                         
                )
               OR
               instr(aia.description,'/'||'{2}',1)>0 
        )   expenseinfo --费用型的采购，或者预付款发票，取他们      
  
  WHERE ph.segment1='{3}'
  AND   nvl(ph.cancel_flag,'N')='N'
  AND   nvl(ph.CLOSED_CODE,'@@')<>'FINALLY CLOSED'
  AND   pla.po_header_id=ph.po_header_id
  AND   nvl(pla.cancel_flag,'N')='N'
  AND   plla.po_line_id=pla.po_line_id
  AND   nvl(plla.cancel_flag,'N')='N'
  AND   aps.vendor_id=ph.vendor_id
  GROUP BY ph.segment1,aps.vendor_name,ph.comments,ph.vendor_site_id,invoiceinfo.invoice_amount,
invoiceinfo.amount_paid,expenseinfo.expense_amount,expenseinfo.amountpaid_expense) a", ContractNum, ContractNum, ContractNum,ContractNum);

                DataTable dt = DataAccess.Instance("FromEBS").ExecuteDataTable(sb.ToString());
                if (dt.Rows.Count == 0)
                {
                    Response.Write("<script>alert('没有查出数据!');</script>");
                }
                else
                {
                    RepeaterItemInfo.DataSource = dt;
                    RepeaterItemInfo.DataBind();
                }
            }
        }
    }
}